package com.caifu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.caifu.pojo.SysOrganization;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 平台组织表 Mapper 接口
 * </p>
 *
 * @author szt
 * @since 2020-08-05
 */
@Mapper
public interface SysOrganizationMapper extends BaseMapper<SysOrganization> {

	/**
	 * 父查子
	 * 2020年8月9日
	 * codeBy szt
	 */
	@Select("SELECT u2.org_id as \"orgId\", u2.org_name as \"orgName\", u2.parent_org_id as \"parentOrgId\", u2.org_level as \"orgLevel\", u2.is_child_exist as \"isChildExist\",  u2.is_admin_institute as \"isAdminInstitute\" FROM( SELECT @ids AS p_ids, (SELECT @ids := GROUP_CONCAT(org_id) FROM sys_organization WHERE FIND_IN_SET(parent_org_id, @ids)) AS c_ids, @l := @l+1 AS LEVEL FROM sys_organization, (SELECT @ids := #{inParentOrgId}, @l := 0 ) b  WHERE @ids IS NOT NULL ) u1 JOIN sys_organization u2 ON FIND_IN_SET(u2.org_id, u1.p_ids) AND u2.state='1' ")
	List<Map<String,Object>> getAllOrganization(@Param("inParentOrgId") String inParentOrgId);

	/**
	 * 子查祖先
	 * 2020年8月9日
	 * codeBy szt
	 */
	@Select("SELECT u2.org_id as \"orgId\", u2.org_name as \"orgName\" FROM( SELECT @id c_ids, (SELECT @id:=GROUP_CONCAT(parent_org_id) FROM sys_organization WHERE FIND_IN_SET(org_id,@id)) p_ids, @l := @l+1 AS LEVEL FROM sys_organization,  (SELECT @id:=#{inOrgId}, @l := 0)b WHERE @id IS NOT NULL) u1 JOIN sys_organization u2  ON u1.c_ids = u2.org_id")
	List<Map<String,Object>> getAncestorOrganization(@Param("inOrgId") String inOrgId);

	/**
	 * 父查子（同时查询有没有赋权）
	 * 2020年8月9日
	 * codeBy szt
	 */
	@Select("SELECT u2.org_id as \"orgId\",u2.type as \"type\", u2.org_name as \"orgName\", u2.parent_org_id as \"parentOrgId\", u2.org_level as \"orgLevel\", u2.is_child_exist as \"isChildExist\",  u2.is_admin_institute as \"isAdminInstitute\" ,case when u3.auth_no is null then 0 else 1 end as \"authType\" FROM( SELECT @ids AS p_ids, (SELECT @ids := GROUP_CONCAT(org_id) FROM sys_organization WHERE FIND_IN_SET(parent_org_id, @ids)) AS c_ids, @l := @l+1 AS LEVEL FROM sys_organization,  (SELECT @ids := #{inParentOrgId}, @l := 0 )b WHERE @ids IS NOT NULL) u1 JOIN sys_organization u2 ON FIND_IN_SET(u2.org_id, u1.p_ids) AND u2.state='1' LEFT JOIN ( select * from sys_user_data_auth s where s.user_no=#{userNo} and s.type='1' )u3 on u2.org_id=u3.org_id ")
	List<Map<String,Object>> getAllAuthOrganization(@Param("inParentOrgId") String inParentOrgId,@Param("userNo") String userNo);


	/**
	 * 供餐机构父查子（同时查询有没有赋权, 并标注已赋权过的组织）
	 * 2020年8月9日
	 * codeBy szt
	 */
	@Select("SELECT u2.org_id as \"orgId\", u2.org_name as \"orgName\", u2.parent_org_id as \"parentOrgId\", u2.org_level as \"orgLevel\", u2.is_child_exist as \"isChildExist\",  u2.is_admin_institute as \"isAdminInstitute\" ,case when u3.auth_no is null then 0 else 1 end as \"authType\", (select count(*) from sys_user_data_auth da left join sys_user u on da.user_no=u.USER_NO where u.POST_TYPE=2 and u.status='1' and da.org_id=u2.org_id and da.user_no!=#{userNo}) as cnt FROM( SELECT @ids AS p_ids, (SELECT @ids := GROUP_CONCAT(org_id) FROM sys_organization WHERE FIND_IN_SET(parent_org_id, @ids)) AS c_ids, @l := @l+1 AS LEVEL FROM sys_organization,  (SELECT @ids := #{inParentOrgId}, @l := 0 )b WHERE @ids IS NOT NULL) u1 JOIN sys_organization u2 ON FIND_IN_SET(u2.org_id, u1.p_ids) AND u2.state='1' LEFT JOIN ( select * from sys_user_data_auth s where s.user_no=#{userNo} and s.type='1' )u3 on u2.org_id=u3.org_id ")
	List<Map<String,Object>> getGongcanAllAuthOrganization(@Param("inParentOrgId") String inParentOrgId,@Param("userNo") String userNo);

	/**
	 * 查询一个组织机构下有多少层级（包含该组织机构）
	 * 2020年8月9日
	 * codeBy szt
	 */
	@Select("SELECT count(distinct u2.org_level) FROM( SELECT @ids AS p_ids, (SELECT @ids := GROUP_CONCAT(org_id) FROM sys_organization WHERE FIND_IN_SET(parent_org_id, @ids)) AS c_ids, @l := @l+1 AS LEVEL FROM sys_organization, (SELECT @ids := #{inParentOrgId}, @l := 0 ) b  WHERE @ids IS NOT NULL ) u1 JOIN sys_organization u2 ON FIND_IN_SET(u2.org_id, u1.p_ids) AND u2.state='1' ")
	int countAllOrganizationLevel(@Param("inParentOrgId") String inParentOrgId);

	/**
	 * 功能描述: 获取机构全路径
	 * @Author: szt
	 * @Date:   2020/11/9
	 */
	@Select("SELECT GROUP_CONCAT(u2.org_name) FROM( SELECT @id c_ids, (SELECT @id:=GROUP_CONCAT(parent_org_id) FROM sys_organization WHERE FIND_IN_SET(org_id,@id)) p_ids, @l := @l+1 AS LEVEL FROM sys_organization, (SELECT @id:=#{orgId}, @l := 0)b WHERE @id IS NOT NULL order by level desc ) u1 JOIN sys_organization u2 ON u1.c_ids = u2.org_id")
	String getOrgPath(@Param("orgId")long orgId);

	/**
	 * 功能描述: 获取机构全路径代码
	 * @Author: szt
	 * @Date:   2020/11/9
	 */
	@Select("SELECT GROUP_CONCAT(u2.org_id) FROM( SELECT @id c_ids, (SELECT @id:=GROUP_CONCAT(parent_org_id) FROM sys_organization WHERE FIND_IN_SET(org_id,@id)) p_ids, @l := @l+1 AS LEVEL FROM sys_organization, (SELECT @id:=#{orgId}, @l := 0)b WHERE @id IS NOT NULL order by level desc ) u1 JOIN sys_organization u2 ON u1.c_ids = u2.org_id")
	String getOrgPathCode(@Param("orgId")long orgId);

	/**
	 * 功能描述: 获取组织类型全路径集合
	 * @Author: szt
	 * @Date:   2020/11/9
	 */
	@Select(" <script>"+
			" select GROUP_CONCAT(s.org_path_code) from sys_user_data_auth a left join sys_organization s on a.org_id=s.org_id" +
			" <if test='userNo != null and userNo != &quot;&quot;'>"+
	        " and a.user_no=${userNo}"+
			" </if>"+
	        " and s.org_name is not null and s.type=${type}"+
	        " </script>")
	String getAllOrgTypePathCode(@Param("userNo")String userNo, @Param("type")String type);

	/**
	 * 通过userNo和类型获取组织架构
	 * @param param
	 * @return
	 */
	List<Map<String, Object>> selectOrgIdsByUserNoAndType(Map<String, Object> param);


	/**
	 * 功能描述: 获取供餐机构重复授权的组织
	 * @Author: szt
	 * @Date:   2020/11/9
	 */
	@Select("select da.org_id,count(*) from sys_user_data_auth da left JOIN sys_user u on da.user_no=u.USER_NO where u.POST_TYPE='2' and u.`STATUS`='1' group by da.org_id having COUNT(*)>1")
	List<Map<String, Object>> getDupGongcanAuthOrg();
}
